package com.bysj.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bysj.dao.ManagerDao;
import com.bysj.entity.Manager;
import com.bysj.util.Dbutils;

public class ManagerDaoImpl implements ManagerDao {
private Dbutils db=new Dbutils();
	@Override
	public void addManager(String sql,Manager manager) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, manager.getName());
			pst.setInt(2, manager.getAge());
			pst.setString(3, manager.getTel());
			pst.setString(4, manager.getEmail());
			pst.setString(5, manager.getPasswd());
			pst.setString(6, manager.getPic());
			pst.setString(7, manager.getUmanager());
			pst.setString(8, manager.getAmanager());
			pst.setString(9, manager.getBmanager());
			pst.setString(10, manager.getRmanager());
			pst.setString(11, manager.getGmanager());
			pst.setString(12, manager.getNmanager());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void delManager(String sql,Manager manager) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void updateManager(String sql,Manager manager) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, manager.getName());
			pst.setInt(2, manager.getAge());
			pst.setString(3, manager.getTel());
			pst.setString(4, manager.getEmail());
			pst.setString(5, manager.getPasswd());
			pst.setString(6, manager.getPic());
			pst.setString(7, manager.getUmanager());
			pst.setString(8, manager.getAmanager());
			pst.setString(9, manager.getBmanager());
			pst.setString(10, manager.getRmanager());
			pst.setString(11, manager.getGmanager());
			pst.setString(12, manager.getNmanager());
			pst.setInt(13, manager.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public Manager queryManager(String sql,Manager manager) {
		Connection	conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Manager m=new Manager();
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, manager.getName());
			pst.setString(2, manager.getPasswd());
			set=pst.executeQuery();
			if(set.next()){
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setAge(set.getInt(3));
				m.setTel(set.getString(4));
				m.setEmail(set.getString(5));
				m.setPasswd(set.getString(6));
				m.setPic(set.getString(7));
				m.setUmanager(set.getString(8));
				m.setAmanager(set.getString(9));
				m.setBmanager(set.getString(10));
				m.setRmanager(set.getString(11));
				m.setGmanager(set.getString(12));
				m.setNmanager(set.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return m;
	}

	@Override
	public ArrayList<Manager> queryAllManager(String sql) {
		ArrayList<Manager> list=new ArrayList<Manager>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Manager m=null;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				m=new Manager();
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setAge(set.getInt(3));
				m.setTel(set.getString(4));
				m.setEmail(set.getString(5));
				m.setPasswd(set.getString(6));
				m.setPic(set.getString(7));
				m.setUmanager(set.getString(8));
				m.setAmanager(set.getString(9));
				m.setBmanager(set.getString(10));
				m.setRmanager(set.getString(11));
				m.setGmanager(set.getString(12));
				m.setNmanager(set.getString(13));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public int CountAllManager(String sql) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		int count=0;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			if(set.next()){
				count=set.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return count;
	}

	@Override
	public ArrayList<Manager> FenyyeQuery(String sql, int page, int rows) {
		ArrayList<Manager> list=new ArrayList<Manager>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Manager m=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			set=pst.executeQuery();
			while(set.next()){
				m=new Manager();
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setAge(set.getInt(3));
				m.setTel(set.getString(4));
				m.setEmail(set.getString(5));
				m.setPasswd(set.getString(6));
				m.setPic(set.getString(7));
				m.setUmanager(set.getString(8));
				m.setAmanager(set.getString(9));
				m.setBmanager(set.getString(10));
				m.setRmanager(set.getString(11));
				m.setGmanager(set.getString(12));
				m.setNmanager(set.getString(13));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public boolean isEnter(String sql,Manager m) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		boolean b=false;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, m.getName());
			pst.setString(2, m.getPasswd());
			if(pst.executeQuery().next()){
				b=true;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
		return b;
	}

	@Override
	public Manager queryManagerById(String sql, Manager ma) {
		Connection	conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Manager m=new Manager();
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, ma.getId());
			set=pst.executeQuery();
			if(set.next()){
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setAge(set.getInt(3));
				m.setTel(set.getString(4));
				m.setEmail(set.getString(5));
				m.setPasswd(set.getString(6));
				m.setPic(set.getString(7));
				m.setUmanager(set.getString(8));
				m.setAmanager(set.getString(9));
				m.setBmanager(set.getString(10));
				m.setRmanager(set.getString(11));
				m.setGmanager(set.getString(12));
				m.setNmanager(set.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return m;
	}

	@Override
	public void updateManagerPicById(String sql, Manager m) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, m.getPic());
			pst.setInt(2, m.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
		
	}

	@Override
	public void updateManagerPasswd(String sql, Manager manager) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, manager.getPasswd());
			pst.setInt(2, manager.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}
}
